package org.anyline.simple.special;

import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.data.run.Run;
import org.anyline.entity.Compare;
import org.anyline.entity.DataRow;
import org.anyline.entity.DataSet;
import org.anyline.metadata.Table;
import org.anyline.service.AnylineService;
import org.anyline.util.BeanUtil;
import org.anyline.util.ConfigTable;
import org.junit.jupiter.api.Test;
import org.junit.platform.commons.logging.Logger;
import org.junit.platform.commons.logging.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@SpringBootTest
public class ServiceTest {
    private Logger log = LoggerFactory.getLogger(ServiceTest.class);
    @Autowired
    private AnylineService service;

    @Test
    public void configs() throws Exception{
        init();
        ConfigTable.IS_SQL_DELIMITER_OPEN = true;
        ConfigStore a1 = new DefaultConfigStore().and(  "id11", 11).and("id12", 12);
        ConfigStore a2 = new DefaultConfigStore().and(  "id21", 21).and("id22", 22);
        ConfigStore a3 = new DefaultConfigStore().and(  "id31", 31).and("id32", 32);
        ConfigStore a = new DefaultConfigStore();
        //a.integrality(false); //删除最外层的()
        //以下两行作用是一样的\\
        //最外层加一个a，a1,a2,a3都作为一个整体加入a
        service.querys("crm_user", a.and(a1, false).or(a2, false).or(a3, false));
        //因为a1中有11,12 在a2加入时需要把11,12合并成一个，不让a2与11，12平级
        service.querys("crm_user", a1.or(a2, true).or(a3, false));

        //apart是指合并条件时，新条件是否插入到原条件的那个list中
        //integrality是指生成完了SQL后 是否需要在最外层加()

        ConfigStore c1 = new DefaultConfigStore();
        c1.and("id = 11").and("id = 12");
        ConfigStore c2 = new DefaultConfigStore();
        c2.and("id = 21").and("id = 22");
        ConfigStore c3 = new DefaultConfigStore();
        c3.and("id = 31").and("id = 32");
        c1.or(c2).or(c3);
        //service.querys("crm_user", c1);

        ConfigStore s1 = new DefaultConfigStore();
        s1.like("name","zh").and("id","11").and("id "," 12");
        ConfigStore s2 = new DefaultConfigStore();
        s2.and("id","21").and("id "," 22");
        ConfigStore s3 = new DefaultConfigStore();
        s3.and("id","31").and("id "," 32");
        s1.or(s2).and(s3, false);
        s1.integrality(false);
        service.querys("crm_user",s1 ,"id:99");
        List<Run> runs = s1.runs();
        for (Run run:runs){
            System.out.println("无占位符 sql:"+run.getFinalQuery(false));
            System.out.println("有占位符 sql:"+run.getFinalQuery());
            System.out.println("占位values:"+run.getValues());
        }

    }
    public void init(){
        try{

            Table table = service.metadata().table("crm_user", false);
            if(null != table){
                service.ddl().drop(table);
            }
            table = new Table("crm_user");
            table.addColumn("id", "bigint").setAutoIncrement(true);
            table.addColumn("id11", "int");
            table.addColumn("id12", "int");
            table.addColumn("id13", "int");
            table.addColumn("id21", "int");
            table.addColumn("id22", "int");
            table.addColumn("id23", "int");
            table.addColumn("id31", "int");
            table.addColumn("id32", "int");
            table.addColumn("id33", "int");
            table.addColumn("age", "int");
            table.addColumn("sex", "int");
            table.addColumn("IDX", "int");
            table.addColumn("REMARK", "varchar(10)");
            table.addColumn("code", "varchar(10)");
            table.addColumn("name", "varchar(10)");
            table.addColumn("nm", "varchar(10)");
            table.addColumn("up_Status", "varchar(10)");
            table.addColumn("val", "varchar(10)");
            table.addColumn("BT","blob");

            service.ddl().create(table);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    @Test
    public void override(){
        init();
        DataRow row = new DataRow();
        row.put("ID",1);
        row.put("CODE", "A");

        //如果数据已存存则覆盖
        row.setOverride(true);
        service.save("crm_user",row);

        //DataSet DataRow规则一样
        row = service.query("crm_user");

        //默认情况下 有主键值则update 没主键则insert
        service.save(row);

        //如果数据已存存则覆盖
        row.setOverride(true);
        service.save(row);


        //如果数据已存存则跳过
        row.setOverride(false);
        service.save(row);
        row.put("ID",2);
        //复合主键规则一样
        row.setPrimaryKey("CODE","NAME");

        //默认情况下 有主键值则update 没主键则insert
        row.setOverride(null);
        service.save(row);

        //如果数据已存存则覆盖 注意这里主键已经设置成了CODE","NAME"
        try {
            row.setOverride(true);
            service.save(row);
        }catch (Exception e){}

        //如果数据已存存则跳过  注意这里主键已经设置成了CODE","NAME"
        try {
            row.setOverride(false);
            service.save(row);
        }catch (Exception e){}
    }
    @Test
    public void json(){
        //JS列设置成json类型
        //ConfigTable.IS_AUTO_CHECK_METADATA = true;
        DataRow user = service.query("CRM_USER");
        user.put("JS","{\"id\":1}");
        user.clearUpdateColumns();
        service.update(user);
        user = service.query("CRM_USER");
        System.out.println(user.toJSON());
    }
    @Test
    public void blob(){
        //BT 设置成blob类型
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        DataRow row = new DataRow();
        row.put("BT", "ABC");
        service.truncate("crm_user");
        service.insert("crm_user", row);
        DataRow user = service.query("CRM_USER");
        System.out.println(user.toJSON());
        System.out.println("blob:"+new String((byte[])user.get("BT")));
        user.put("BT","abc".getBytes());
        user.clearUpdateColumns();
        service.update(user);
    }

    @Test
    public void query(){
        service.query("CRM_USER", "+id:null"); // ID IS NULL
        service.query("CRM_USER", "+id:");     // ID IS NULL
        //关于几个 空值 的查询条件
        ConfigStore store = new DefaultConfigStore();
        store.and("+ID", null);                // ID IS NULL
        store.and("+REMARK", "");              // REMARK = ''
        store.and("+IDX", "".split(","));      // IDX = ''
        store.and("+CODE", new ArrayList<>());       // CODE IS NULL
        store.and("+VAL", new String[]{});           // VAL IS NULL
        DataSet set = service.querys("CRM_USER(ID, CODE,NM,VAL)", store);


        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        //反例 这里不能这样直接拼上，因为list.toString返回的结果会有空格[1, 2, 3]
        service.query("CRM_USER","ID:"+ids, "(id>0 and age>10)");
        service.query("CRM_USER","ID:"+BeanUtil.list2string(ids));
    }
    @Test
    public void update(){
        DataRow row = new DataRow();
        row.put("-REMARK","不更新,不插入");	//添加到row中 但不参与插入(更新)
        row.put("+CODE", null);				//默认情况这值不参与插入(更新)， +表示强制参与插入(更新)
        service.update("CRM_USER", row);
        //只更新CODE REMARK
        service.update("CRM_USER",row, "CODE", "REMARK");
        //CODE强制更新 其他按默认情况(但不包括已忽略的列)
        service.update("CRM_USER",row,"+CODE");
        //只更新值有变化的列(但不包括已忽略的列)
        service.update("CRM_USER",row);
    }
    @Test
    public void delete(){
        //根据 ID 删除多行
        try {
            //注意:为了避免整表删除,values必须提供否则会抛出异常
            //整表删除请调用service.execute("DELETE FROM crm_user");
           // service.deletes("crm_user", "ID");
        }catch (Exception e){
            e.printStackTrace();
        }
        //DELETE FROM crm_user WHERE ID = 100
        service.deletes("crm_user", "ID", "100");
        //DELETE FROM crm_user WHERE ID IN(100,200)
        service.deletes("crm_user", "ID", "100","200");

        service.deletes("crm_user", "ID", 1,2);

        List<String> ids = new ArrayList<>();
        //注意:为了避免整表删除,ids必须提供否则会抛出异常
        //service.deletes("crm_user", "ID", ids);
        ids.add("100");
        ids.add("200");
        service.deletes("crm_user", "ID", ids);

        //根据多列条件删除
        //DELETE FROM crm_user WHERE ID = 1 AND NM = 'ZH'
        DataRow row = new DataRow();
        row.put("ID","1");
        row.put("NM", "ZH");
        service.delete("crm_user", row, "ID","NM");

        //DELETE FROM crm_user WHERE ID = 1 AND CODE = 20
        service.delete("crm_user","ID","1", "CODE:20");

        //DELETE FROM crm_user WHERE ID = '' AND CODE = 20
        service.delete("crm_user","ID","", "CODE:20");
        //DELETE FROM crm_user WHERE ID = 1 AND CODE = ''
        service.delete("crm_user","ID","1", "CODE:");


    }
    @Test
    public void set(){
        DataSet set = new DataSet();
        DataRow row = new DataRow();
        row.put("ID",1);
        row.put("NAME", "张三");
        set.add(row);

        //value可以是正则表达式,也可以是SQL通配符
        DataSet result = set.select.like("NAME","张%");
        System.out.println(result);
        result = set.getRows("NAME:张%");
        System.out.println(result);
        result = set.getRows("NAME","张%");
        System.out.println(result);
    }

    @Test
    public void map(){
        Map<String,String> map = new HashMap<>();
        ConfigTable.IS_UPPER_KEY = false;
        map.put("ID","1");
        map.put("up_Status", "2");
        service.update("crm_user", map, "up_Status");
        service.insert("crm_user", map);
    }
    @Test
    public void list(){
        DataRow row = new DataRow();
        List<String> codes = new ArrayList<>();
        codes.add("111");
        codes.add("222");
        row.put("ID", codes);
        row.put("CODE", codes);
        service.save("crm_user", row);
        /*UPDATE crm_user SET CODE = 1111 WHERE 1=1 AND `ID` IN (111,222)*/
        service.querys("crm_user");
    }
    @Test
    public void or(){
        ConfigStore configs = new DefaultConfigStore();
        configs.and("ID", 1).or("NM","ZH").ors("SEX","1");
        configs.and(Compare.LIKE, "nm","zh");
        service.querys("crm_user", configs);
    }
}
